using System;
using System.Text;
using System.Reflection;
using System.Collections.Generic;
using System.Data.SqlClient;

using Open.Data.Persistence.Definitions;

namespace Open.Data.Persistence.SqlServer
{
    //Implements deployment of types to a SqlServer database
    public class SqlDeployment: Deployment
    {
        public SqlDeployment(): base()
        {

        }

        public SqlDeployment(SqlContext context): base(context)
        {

        }

        #region Overrides

        public override void CreateDatabase(string name)
        {
            System.Data.SqlClient.SqlConnection connection = null;
            System.Data.SqlClient.SqlCommand command = null;

            try
            {
                //Get a connection to master and create the database
                //Context database has to be set before connection is requested
                connection = GetConnection("master");
                command = connection.CreateCommand();

                command.CommandText = "CREATE DATABASE " + name;
                command.ExecuteNonQuery();
            }
            finally
            {
                if (command != null) command.Dispose();
                if (connection != null) connection.Dispose();
            }
        }

        public override string[] GetDatabaseNames()
        {
            System.Data.SqlClient.SqlConnection connection = null;
            System.Data.SqlClient.SqlCommand command = null;

            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT name FROM sys.databases ");
            builder.Append("WHERE name <> 'master' ");
            builder.Append("AND name <> 'tempdb' ");
            builder.Append("AND name <> 'model' ");
            builder.Append("AND name <> 'msdb'");

            List<string> names = new List<string>();

            try
            {
                //Get a connection to master and create the database
                connection = GetConnection("master");
                command = connection.CreateCommand();
                command.CommandText = builder.ToString();
                
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    names.Add(reader.GetString(0));
                }

                reader.Close();
            }
            finally
            {
                if (command != null) command.Dispose();
                if (connection != null) connection.Dispose();
            }

            return names.ToArray();
        }

        public override string GetTableName(Type type)
        {
            return Common.GetTypeName(type, Context.Prefix);
        }

        public override string SqlFromType(Type type)
        {
            return SqlCommon.SqlFromType(type);
        }

        public override string DefaultFromType(Type type)
        {
            return SqlCommon.DefaultFromType(type);
        }

        public override bool Exists(Type type)
        {
            return TableExists(GetTableName(type));
        }

        public override bool HasRows(Type type)
        {
            string name = GetTableName(type);

            //Get the number of rows
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT COUNT(*) FROM [");
            builder.Append(name);
            builder.Append("]");

            System.Data.SqlClient.SqlConnection connection = GetConnection();
            System.Data.SqlClient.SqlCommand command = connection.CreateCommand();

            command.CommandText = builder.ToString();

            int? result = null;

            try
            {
                result = (int?)command.ExecuteScalar();
            }
            finally
            {
                command.Dispose();
                connection.Dispose();
            }

            return (result.HasValue && result.Value > 0);
        }

        public override TableDefinition GetTableDefinition(Type type)
        {
            TableDefinition definition = new TableDefinition();
            string name = GetTableName(type);

            //Get the column information
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT COLUMN_NAME, ISNULL(CHARACTER_MAXIMUM_LENGTH,0) AS MAXIMUM_LENGTH, DATA_TYPE, IS_NULLABLE, ISNULL(NUMERIC_PRECISION,0) AS NUMERIC_PRECISION, ISNULL(NUMERIC_SCALE,0) AS NUMERIC_SCALE,");
            builder.Append("columnproperty(object_id(TABLE_NAME),COLUMN_NAME,'IsIdentity')  AS ISIDENTITY ");
            builder.Append("FROM INFORMATION_SCHEMA.COLUMNS ");
            builder.Append("WHERE TABLE_NAME = '");
            builder.Append(name);
            builder.Append("'");

            System.Data.SqlClient.SqlConnection connection = GetConnection();
            System.Data.SqlClient.SqlCommand command = connection.CreateCommand();

            command.CommandText = builder.ToString();

            try
            {
                System.Data.SqlClient.SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    definition.Names.Add(reader.GetString(0));
                    definition.MaxLengths.Add(reader.GetInt32(1));
                    definition.Types.Add(reader.GetString(2));
                    definition.Nulls.Add(reader.GetString(3).ToUpper() == "YES");
                    definition.Precisions.Add(int.Parse(reader.GetValue(4).ToString()));
                    definition.Scales.Add(int.Parse(reader.GetValue(5).ToString()));
                    definition.Identities.Add(!reader.IsDBNull(6) && reader.GetInt32(6) == 1);
                }

                reader.Close();
            }
            finally
            {
                command.Dispose();
                connection.Dispose();
            }

            return definition;
        }

        public override KeyDefinition GetKeyDefinition(Type type)
        {
            //Validate the constraints by getting an ordered list of keys and comparing
            StringBuilder builder = new StringBuilder();

            builder.Append("SELECT cu.COLUMN_NAME, cu.CONSTRAINT_NAME ");
            builder.Append("FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ");
            builder.Append("JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu ON cu.TABLE_NAME = tc.TABLE_NAME ");
            builder.Append("AND	cu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME ");
            builder.Append("WHERE tc.TABLE_NAME = '");
            builder.Append(GetTableName(type));
            builder.Append("' AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY' ");
            builder.Append("ORDER BY cu.[ORDINAL_POSITION]");

            System.Data.SqlClient.SqlConnection connection = GetConnection();
            System.Data.SqlClient.SqlCommand command = connection.CreateCommand();
            command.CommandText = builder.ToString();

            KeyDefinition definition = new KeyDefinition();

            try
            {
                System.Data.SqlClient.SqlDataReader reader = command.ExecuteReader();

                //Load keys into a list
                while (reader.Read())
                {
                    definition.Keys.Add(reader.GetString(0));
                    definition.Constraint = reader.GetString(1);
                }
            }
            finally
            {
                command.Dispose();
                connection.Dispose();
            }

            return definition;
        }

        public override bool HasChanged(Type type, TableDefinition tableDefinition, KeyDefinition keyDefinition)
        {
            //Get the properties from the type
            SqlCacheItem cache = SqlProvider.Instance.ReflectionCache.Lookup(type) as SqlCacheItem;
            string propname;
            int index;

            //Loop through each property          
            foreach (PropertyInfo prop in cache.GetAllProperties())
            {
                propname = SqlFormatter.GetPropertyName(prop, ".");
                object[] attributes = prop.GetCustomAttributes(false);

                //Find matching name from data definition
                index = tableDefinition.Names.IndexOf(propname);

                //If not found then create, else alter
                if (index == -1) return true;

                //Detect if data type has changed
                if (tableDefinition.Types[index].ToLower() != SqlCommon.SqlFromType(prop.PropertyType).ToLower()) return true;

                //Detetc attribute changes here
                foreach (Attribute attr in attributes)
                {
                    //Detect length of strings here
                    if (prop.PropertyType == typeof(string) && attr is SqlLengthAttribute)
                    {
                        var max = attr as SqlLengthAttribute;
                        if (max.Length != tableDefinition.MaxLengths[index]) return true;
                    }

                    //Detect floating point changes here
                    if (attr is SqlFixedAttribute)
                    {
                        var fixedAttr = attr as SqlFixedAttribute;
                        if (fixedAttr.Precision != tableDefinition.Precisions[index] || fixedAttr.Scale != tableDefinition.Scales[index]) return true;
                    }

                    //Identity changes
                    if (attr is SqlIdentityAttribute)
                    {
                        var identityAttr = attr as SqlIdentityAttribute;
                        if (!tableDefinition.Identities[index]) return true;
                    }
                }

                //Detect if nullability has changed
                if (tableDefinition.Nulls[index] != GetTypeNullability(prop.PropertyType)) return true;

                //Detect if identity has ben removed
                if (tableDefinition.Identities[index] && !cache.IsIdentity) return true;
            }

            //Type must have a key
            if (keyDefinition.Keys.Count == 0) return true;

            //Check primary key
            if (cache.HasPrimaryKey)
            {
                if (cache.PrimaryKey.Name.ToLower() != keyDefinition.Keys[0].ToLower()) return true;
            }
            
            //Check compound keys
            if (cache.HasCompoundKeys)
            {
                //Loop through compound keys and check their matching names
                int i = 0;
                foreach (PropertyInfo prop in cache.CompoundKeys)
                {
                    if (SqlFormatter.GetPropertyName(prop, ".").ToLower() != keyDefinition.Keys[i].ToLower())
                    {
                        return true;
                    }
                    i++;
                }
            }

            //Check for orphaned fields that are not null
            TableDefinition orphans = GetOrphans(tableDefinition, cache);

            foreach (bool isnull in orphans.Nulls)
            {
                if (!isnull) return true;
            }

            return false;
        }

        public override TableModification GetTableModification(Type type, TableDefinition definition)
        {
            //Get the properties from the type
            TableModification modification = new TableModification();
            CacheItem cache = Provider.Instance.ReflectionCache.Lookup(type);
            string propname;
            int index;

            //Loop through each property and determine if it must be added or changed   
            PropertyInfo[] properties = cache.GetAllProperties();

            foreach (PropertyInfo prop in properties)
            {
                propname = Formatter.GetPropertyName(prop, ".");

                //Find matching name from data definition
                index = definition.Names.IndexOf(propname);

                //If not found then add
                if (index == -1)
                {
                    modification.Added.Add(prop);
                }
                else
                {
                    bool flag = false;

                    //Detect if data type has changed
                    if (definition.Types[index].ToLower() != SqlFromType(prop.PropertyType).ToLower())
                    {
                        modification.Changed.Add(prop);
                        flag = true;
                    }
                    else if (definition.Nulls[index] != GetTypeNullability(prop.PropertyType))
                    {
                        modification.Changed.Add(prop);
                        flag = true;
                    }

                    //Check attribute changes
                    object[] attributes = prop.GetCustomAttributes(false);
                    foreach (Attribute attr in attributes)
                    {
                        //Detect length of strings here
                        if (prop.PropertyType == typeof(string) && attr is SqlLengthAttribute)
                        {
                            var max = attr as SqlLengthAttribute;
                            if (max.Length != definition.MaxLengths[index])
                            {
                                modification.Changed.Add(prop);
                                flag = true;
                                break;
                            }
                        }

                        //Detect floating point changes here
                        if (attr is SqlFixedAttribute)
                        {
                            var fixedAttr = attr as SqlFixedAttribute;
                            if (fixedAttr.Precision != definition.Precisions[index] || fixedAttr.Scale != definition.Scales[index])
                            {
                                modification.Changed.Add(prop);
                                flag = true;
                                break;
                            }
                        }

                        //Identity changes
                        if (attr is SqlIdentityAttribute)
                        {
                            var identityAttr = attr as SqlIdentityAttribute;
                            if (!definition.Identities[index])
                            {
                                modification.Changed.Add(prop);
                                flag = true;
                                break;
                            }
                        }
                    } 

                    //Add to unchanged if no changes
                    if (!flag) modification.Unchanged.Add(prop);
                }
            }

            return modification;
        }

        public override void CreateTable(StringBuilder builder, Type type, string name)
        {
            builder.Append("CREATE TABLE ");
            builder.Append("[");
            builder.Append(name);
            builder.Append("]  \r\n(");

            //Loop through all the properties and create 
            SqlCacheItem cache = SqlProvider.Instance.ReflectionCache.Lookup(type) as SqlCacheItem;

            bool flag = false;

            //Append any key columns to the table
            foreach (PropertyInfo prop in cache.GetKeyProperties())
            {
                string propertyName = SqlFormatter.GetPropertyName(prop, ".");

                if (flag) builder.Append(",");

                builder.Append("[");
                builder.Append(propertyName);
                builder.Append("]");
                builder.Append(" ");
                builder.Append(SqlCommon.SqlFromProperty(prop));

                //Add identity information if required
                if (prop == cache.Identity)
                {
                    SqlIdentityAttribute key = cache.GetIdentityAttribute();

                    if (cache.IsUnderlying && key != null && key.Increment > 0)
                    {
                        builder.Append(" IDENTITY(");
                        builder.Append(key.Start);
                        builder.Append(",");
                        builder.Append(key.Increment);
                        builder.Append(")");
                    }
                }

                builder.Append(" NOT NULL");

                flag = true;
            }

            foreach (PropertyInfo prop in cache.Properties)
            {
                string propertyName = SqlFormatter.GetPropertyName(prop, ".");

                if (flag) builder.Append(", \r\n");

                //Get the type of the key
                builder.Append("[");
                builder.Append(propertyName);
                builder.Append("]");
                builder.Append(" ");
                builder.Append(SqlCommon.SqlFromProperty(prop));

                //Add nullability
                if (GetTypeNullability(prop.PropertyType))
                {
                    builder.Append(" NULL ");
                }
                else
                {
                    builder.Append(" NOT NULL DEFAULT ");
                    builder.Append(DefaultFromType(prop.PropertyType));
                }

                flag = true;
            }

            builder.Append(")  \r\n");
        }

        //Remove any foreign key references from this type so that it can be built
        public override void RemoveReferences(StringBuilder builder, Type type)
        {
            StringBuilder references = new StringBuilder();

            references.AppendLine("SELECT rc.CONSTRAINT_NAME, tc.TABLE_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ");
            references.AppendLine("JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ");
            references.AppendLine("ON tc.CONSTRAINT_NAME = rc.CONSTRAINT_NAME ");

            references.AppendLine("JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ");
            references.AppendLine("ON rc.UNIQUE_CONSTRAINT_NAME = tc2.CONSTRAINT_NAME ");
            references.Append("WHERE tc2.TABLE_NAME = '");
            references.Append(GetTableName(type));
            references.Append("'");

            System.Data.SqlClient.SqlConnection connection = GetConnection();
            System.Data.SqlClient.SqlCommand command = connection.CreateCommand();

            command.CommandText = references.ToString();

            try
            {
                System.Data.SqlClient.SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        builder.Append("ALTER TABLE [");
                        builder.Append(reader.GetString(1));
                        builder.Append("] DROP CONSTRAINT [");
                        builder.Append(reader.GetString(0));
                        builder.AppendLine("] ");
                    }

                    builder.AppendLine("GO ");
                }
            }
            finally
            {
                command.Dispose();
                connection.Dispose();
            }
        }

        //Remove any stored procedures from this type
        public override void RemoveProcedures(StringBuilder builder, Type type)
        {
            StringBuilder procedures = new StringBuilder();

            procedures.AppendLine("SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES ");
            procedures.AppendLine("WHERE ROUTINE_TYPE='PROCEDURE' ");
            procedures.Append("AND ROUTINE_NAME LIKE '");
            procedures.Append(GetTableName(type));
            procedures.Append(".%'");

            System.Data.SqlClient.SqlConnection connection = GetConnection();
            System.Data.SqlClient.SqlCommand command = connection.CreateCommand();

            command.CommandText = procedures.ToString();

            try
            {
                System.Data.SqlClient.SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        builder.Append("DROP PROCEDURE [");
                        builder.Append(reader.GetString(0));
                        builder.AppendLine("] ");
                    }

                    builder.AppendLine("GO ");
                }
            }
            finally
            {
                command.Dispose();
                connection.Dispose();
            }
        }

        //Add any new Fields. If not null, use the type default.
        //Make sure any removed fields are altered to be null.
        //Any fields with nullability changed will use a default.
        //Any fields with type changes will cause a table copy with a sql convert (if there are rows)
        public override void AlterTable(StringBuilder builder, Type type, TableDefinition definition, TableModification modification)
        {
            //Exit if there are no changes
            if (DeploymentMode == DeploymentMode.Update && modification.Added.Count == 0 && modification.Changed.Count == 0) return;
            
            //Determine if any rows in the table dont relate to the object
            var cache = SqlProvider.Instance.ReflectionCache.Lookup(type) as SqlCacheItem;
            TableDefinition orphan = GetOrphans(definition, cache);

            string tablename = GetTableName(type);

            //Only add rows
            if (DeploymentMode == DeploymentMode.Update && modification.Added.Count > 0 && modification.Changed.Count == 0)
            {
                builder.Append("ALTER TABLE [");
                builder.Append(tablename);
                builder.Append("] \r\n");
                builder.Append("ADD \r\n");

                bool flag = false;

                foreach (PropertyInfo prop in modification.Added)
                {
                    string propertyName = SqlFormatter.GetPropertyName(prop, ".");

                    if (flag) builder.Append(",\r\n");

                    //Get the type of the key
                    builder.Append("[");
                    builder.Append(propertyName);
                    builder.Append("]");
                    builder.Append(" ");
                    builder.Append(SqlCommon.SqlFromProperty(prop));

                    //Add nullability
                    if (GetTypeNullability(prop.PropertyType))
                    {
                        builder.Append(" NULL ");
                    }
                    else
                    {
                        builder.Append(" NOT NULL DEFAULT ");
                        builder.Append(DefaultFromType(prop.PropertyType));
                    }

                    flag = true;
                }

                //Finished
                builder.Append(" \r\nGO \r\n");
                return;
            }

            builder.Append("BEGIN TRANSACTION \r\n");

            //If there are no rows in the table, or we are overwriting data, then recreate it
            if (DeploymentMode == DeploymentMode.Overwrite || !HasRows(type))
            {
                builder.Append("DROP TABLE [");
                builder.Append(tablename);
                builder.Append("] \r\n");

                CreateTable(builder, type, tablename);

                builder.Append("COMMIT \r\nGO \r\n");
                return;
            }

            //Remove temp table if exists
            if (TableExists("tmp_" + tablename))
            {
                builder.Append("DROP TABLE [");
                builder.Append("tmp_");
                builder.Append(tablename);
                builder.Append("] \r\n");
            }

            //Create a new table with the correct data types
            CreateTable(builder, type, "tmp_" + tablename);

            //Add any remaining orphan fields
            //They will always be nullable
            if (orphan.Names.Count > 0)
            {
                builder.Append("ALTER TABLE [");
                builder.Append("tmp_");
                builder.Append(tablename);
                builder.Append("] \r\n");
                builder.Append("ADD \r\n");

                bool flag = false;

                for (int i=0; i<orphan.Names.Count; i++)
                {
                    if (flag) builder.Append(",");

                    builder.Append("[");
                    builder.Append(orphan.Names[i]);
                    builder.Append("]");
                    builder.Append(" ");
                    builder.Append(orphan.Types[i]);
                    if (orphan.MaxLengths[i] > 0)
                    {
                        builder.Append("(");
                        builder.Append(orphan.MaxLengths[i]);
                        builder.Append(")");
                    }
                    builder.Append(" NULL ");

                    flag = true;
                }

                builder.Append(" \r\n");
            }

            //Copy the data across
           
            //Loop through each property and determine how it must be copied
            bool flag2 = false;
            StringBuilder insert = new StringBuilder();
            StringBuilder select = new StringBuilder();

            foreach (PropertyInfo prop in cache.GetAllProperties())
            {
                //Do not process columns that have been added
                if (modification.Added.Contains(prop)) continue;

                if (flag2)
                {
                    insert.Append(",");
                    select.Append(",");
                }

                //Get the source and target column names 
                string propname = SqlFormatter.GetPropertyName(prop, ".");
                propname = string.Format("{0}{1}{2}", "[", propname, "]");

                //If the property is unchanged then just copy it across
                if (modification.Unchanged.Contains(prop))
                {
                    insert.Append(propname);
                    select.Append(propname);
                    flag2 = true;
                    continue;
                }

                //If the type has changed then convert
                if (modification.Changed.Contains(prop))
                {
                    insert.Append(propname);
                    
                    select.Append("CONVERT(");
                    select.Append(SqlCommon.SqlFromType(prop.PropertyType));
                    select.Append(",");
                    select.Append(propname); 
                    select.Append(")");
                    flag2 = true;
                }
            }

            //Add the remaining fields which do not have matching properties
            foreach (string name in orphan.Names)
            {
                if (flag2)
                {
                    insert.Append(",");
                    select.Append(",");
                }

                string nameFormatted = string.Format("{0}{1}{2}", "[", name, "]");
                insert.Append(nameFormatted);
                select.Append(nameFormatted);

                flag2 = true;
            }

            //Set identity insert on if re  uired
            if (cache.IsIdentity) 
            {
                builder.Append("SET IDENTITY_INSERT [tmp_");
                builder.Append(tablename);
                builder.Append("] ON");
            }

            builder.Append("EXEC('INSERT INTO [tmp_");
            builder.Append(tablename);
            builder.Append("] (");
            builder.Append(insert.ToString());
            builder.Append(") \r\nSELECT ");
            builder.Append(select.ToString());
            builder.Append(" FROM [");
            builder.Append(tablename);
            builder.Append("] WITH (HOLDLOCK TABLOCKX)')");
            builder.Append(" \r\n");

            //Set identity insert off
            if (cache.IsIdentity)
            {
                builder.Append("SET IDENTITY_INSERT [tmp_");
                builder.Append(tablename);
                builder.Append("] OFF");
            }

            //Drop the old table
            builder.Append("DROP TABLE [");
            builder.Append(tablename);
            builder.Append("] \r\n");

            //Rename the temp table to the new table
            builder.Append("EXECUTE sp_rename N'[tmp_");
            builder.Append(tablename);
            builder.Append("]', N'");
            builder.Append(tablename);
            builder.Append("', 'OBJECT'");

            builder.Append(" \r\n");
            builder.Append("COMMIT \r\nGO \r\n");

        }
        public override void ExecuteScript(StringBuilder builder)
        {
            System.Data.SqlClient.SqlConnection connection = GetConnection();
            System.Data.SqlClient.SqlCommand command = connection.CreateCommand();

            string[] statements = builder.ToString().Split(new string[] { "GO", "Go", "go", "gO" },StringSplitOptions.RemoveEmptyEntries);
            string lastStatement = null;

            try
            {
                foreach (string statement in statements)
                {
                    string parse = statement.Replace("\r\n", "");
                    parse = parse.Replace(" ", "");

                    if (parse != string.Empty)
                    {
                        lastStatement = statement;
                        command.CommandText = statement;
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException ex)
            {
                throw new DeploymentException(string.Format("An error occurred executing the following statement: \r\n{0}", lastStatement), ex);
            }
            finally
            {
                command.Dispose();
                connection.Dispose();
            }
        }

        //Determines if the primary key for the table is correct and applied.
        public override ValidationResult ValidateKey(StringBuilder builder, Type type, KeyDefinition definition)
        {
            //Get the properties from the type
            CacheItem cache = SqlProvider.Instance.ReflectionCache.Lookup(type);
            SqlPrimaryKeysAttribute attr = new SqlPrimaryKeysAttribute(cache.PrimaryKeyAttribute);
            ValidationResult result = new ValidationResult();

            bool rebuild = false;

            //Only need to check key names, types will have been altered if incorrect
            if (definition.Keys.Count == 0)
            {
                rebuild = true;
            }
            else
            {
                result.Exists = true;

                if (cache.HasPrimaryKey)
                {
                    rebuild = (cache.PrimaryKey.Name.ToLower() != definition.Keys[0].ToLower());
                }
                else if (cache.HasCompoundKeys)
                {
                    //Loop through compound keys and check their matching names
                    int i = 0;
                    foreach (PropertyInfo prop in cache.CompoundKeys)
                    {
                        if (SqlFormatter.GetPropertyName(prop, ".").ToLower() != definition.Keys[i].ToLower())
                        {
                            rebuild = true;
                            break;
                        }
                        i++;
                    }
                }

                //Check sql specific properties
                if (!rebuild)
                {
                    StringBuilder check = new StringBuilder();

                    check.Append("SELECT type FROM sys.indexes WHERE name = '");
                    check.Append(definition.Constraint);
                    check.Append("'");

                    System.Data.SqlClient.SqlConnection connection = GetConnection();
                    System.Data.SqlClient.SqlCommand command = connection.CreateCommand();

                    command.CommandText = check.ToString();
                    object indexExists = command.ExecuteScalar();

                    //Check result is found
                    bool clustered = false;
                    if (indexExists != null) clustered = (int.Parse(indexExists.ToString()) == 1); //1 - clustered, 2 - non-clustered

                    rebuild = (clustered != attr.Clustered);
                }

                //Must first drop if rebuild required
                if (rebuild)
                {
                    builder.Append("ALTER TABLE [");
                    builder.Append(GetTableName(type));
                    builder.Append("] DROP CONSTRAINT [");
                    builder.Append(definition.Constraint);
                    builder.Append("]");
                    builder.Append(" \r\n");
                    
                    result.Changed = true;
                }
            }

            //Rebuild constraint
            if (rebuild)
            {
                builder.Append("ALTER TABLE [");
                builder.Append(GetTableName(type));
                builder.Append("] ADD CONSTRAINT [");
                builder.Append(GetTableName(type));
                builder.Append("Pk");
                builder.Append("] PRIMARY KEY ");
                builder.AppendFormat("{0} ", attr.Clustered ? "CLUSTERED" : "NONCLUSTERED");
                SqlCommon.AppendPrimaryKeyNames(builder, type, cache);
                builder.Append(" \r\n");
            }

            return result;
        }

        //Validate a property reference, which is either an explicit or implicitly defined foreign key
        public override ValidationResult ValidateReference(StringBuilder builder, Type type, PropertyInfo[] properties, Type reftype)
        {
            //Ensure the two tables exists
            StringBuilder check = new StringBuilder();
            ValidationResult result = new ValidationResult();

            check.Append("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '");
            check.Append(GetTableName(type));
            check.Append("' OR TABLE_NAME = '");
            check.Append(GetTableName(reftype));
            check.Append("'");

            System.Data.SqlClient.SqlConnection connection = GetConnection();
            System.Data.SqlClient.SqlCommand command = connection.CreateCommand();

            command.CommandText = check.ToString();
            int count = (int) command.ExecuteScalar();

            if (count != 2) return result;

            //Calculate name of constraint
            StringBuilder constraint = new StringBuilder();

            foreach (PropertyInfo prop in properties)
            {
                constraint.Append(SqlFormatter.GetPropertyName(prop, ""));
            }

            string name = string.Format("{0}{1}Fk", GetTableName(type), constraint.ToString().GetHashCode());

            //Check if constraint exists
            constraint = new StringBuilder();
            constraint.Append("SELECT 1 FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME = '");
            constraint.Append(name);
            constraint.Append("'");

            //Create new foreign key constraint
            command.CommandText = constraint.ToString();
            object exists = null;

            try
            {
                 exists = command.ExecuteScalar();
            }
            finally
            {
                command.Dispose();
                connection.Dispose();
            }

            if (exists == null)
            {
                CacheItem refcache = SqlProvider.Instance.ReflectionCache.Lookup(reftype);

                builder.Append("ALTER TABLE [");
                builder.Append(GetTableName(type));
                builder.Append("] ADD CONSTRAINT [");
                builder.Append(name);
                builder.Append("]");
                builder.Append(" FOREIGN KEY ");

                //Can be the primary key, or an object property, an explicitly defined foreign key column
                //Properties passed in are foreign keys
                builder.Append("([");

                bool flag = false;
                foreach (PropertyInfo prop in properties)
                {
                    if (flag) builder.Append("],[");
                    builder.Append(SqlFormatter.GetPropertyName(prop, "."));
                    flag = true;
                }

                builder.Append("])");

                builder.Append(" REFERENCES [");
                builder.Append(GetTableName(reftype));
                builder.Append("] ");
                SqlCommon.AppendPrimaryKeyNames(builder, reftype, refcache);
                builder.Append(" \r\nGO \r\n");

                result.Changed = true;
            }
            else
            {
                result.Exists = true;
            }

            return result;
        }

        //Validate that an index has been created correctly
        public override ValidationResult ValidateIndex(StringBuilder builder, Type type, PropertyInfo[] properties, IIndexAttribute attribute)
        {
            //Ensure that the table exists
            StringBuilder check = new StringBuilder();
            ValidationResult result = new ValidationResult();

            check.Append("SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '");
            check.Append(GetTableName(type));
            check.Append("'");

            System.Data.SqlClient.SqlConnection connection = GetConnection();
            System.Data.SqlClient.SqlCommand command = connection.CreateCommand();

            command.CommandText = check.ToString();
            object tableExists = command.ExecuteScalar();
            
            //Table not found, return
            if (tableExists == null) return result;

            //Calculate name of index
            StringBuilder constraint = new StringBuilder();
            string tableName = GetTableName(type);

            foreach (PropertyInfo prop in properties)
            {
                constraint.Append(SqlFormatter.GetPropertyName(prop, ""));
            }

            string name = string.Format("{0}{1}Idx", tableName, constraint.ToString().GetHashCode());

            //Check if the index exists
            check = new StringBuilder();

            check.Append("SELECT [type], [is_unique], [fill_factor], [ignore_dup_key] FROM sys.indexes WHERE name = '");
            check.Append(name);
            check.Append("'");

            command = connection.CreateCommand();
            command.CommandText = check.ToString();
            SqlDataReader reader = command.ExecuteReader();
            
            bool exists = false;
            
            bool clustered = false;
            bool isUnique = false;
            byte fillFactor = 0;
            bool ignoreDuplicates = false;

            //The name of index is calculated using a hash of the column names. We do not need to check if they have changed.
            while (reader.Read())
            {
                exists = true;
                result.Exists = true;

                clustered = (reader.GetByte(0) == Convert.ToByte(1)); //type = 1 clustered, 2 nonclustered
                isUnique = reader.GetBoolean(1);
                fillFactor = reader.GetByte(2); //fill factor = 0 is default
                ignoreDuplicates = reader.GetBoolean(3);
            }

            //Create a full sql columns index attribute containing defaults from the vanila index attribute (if required)
            SqlIndexColumnsAttribute sqlAttribute = new SqlIndexColumnsAttribute(attribute);
            
            //Determine if we need to rebuild the index
            bool rebuild = false;

            if (exists)
            {
                if (clustered != sqlAttribute.Clustered) rebuild = true;
                if (isUnique != sqlAttribute.Unique) rebuild = true;
                if (fillFactor != sqlAttribute.FillFactor) rebuild = true;
                if (ignoreDuplicates != sqlAttribute.IgnoreDuplicates) rebuild = true;
            }
            else
            {
                rebuild = true;
            }

            //Drop the index if necessary
            if (exists && rebuild)
            { 
                builder.Append("DROP INDEX [");
                builder.Append(name);
                builder.Append("] ON [dbo].[");
                builder.Append(tableName);
                builder.Append("]");
                builder.Append(" \r\nGO \r\n");

                result.Changed = true;
            }

            //Build the index
            if (rebuild)
            {
                builder.Append("CREATE ");
                if (sqlAttribute.Unique) builder.Append("UNIQUE ");
                builder.AppendFormat("{0} ", sqlAttribute.Clustered ?  "CLUSTERED" : "NONCLUSTERED");
                builder.Append("INDEX [");
                builder.Append(name);
                builder.Append("] ON [dbo].[");
                builder.Append(tableName);
                builder.Append("]");
                builder.Append(" \r\n( \r\n[");

                bool flag = false;
                foreach (PropertyInfo prop in properties)
                {
                    if (flag) builder.Append("],[");
                    builder.Append(SqlFormatter.GetPropertyName(prop, "."));
                    flag = true;
                }
                builder.Append("] \r\n) ");
                builder.AppendFormat("WITH (IGNORE_DUP_KEY = {0}) \r\n", sqlAttribute.IgnoreDuplicates ? "ON" : "OFF");
                builder.Append(" \r\nGO \r\n");
            }

            return result;
        }

        #endregion

        #region Implementation

        private System.Data.SqlClient.SqlConnection GetConnection()
        {
            if (Context == null) throw new DeploymentException("Context is null. Set the context to deploy an assembly.");
            if (!(Context is SqlContext)) throw new DeploymentException("Context is not a SqlContext. Set the context to the correct type to deploy this assembly using this class.");

            SqlContext context = (SqlContext) Context;
            return context.GetConnectionInternal();
        }

        private System.Data.SqlClient.SqlConnection GetConnection(string database)
        {
            if (Context == null) throw new DeploymentException("Context is null. Set the context to deploy an assembly.");
            if (!(Context is SqlContext)) throw new DeploymentException("Context is not a SqlContext. Set the context to the correct type to deploy this assembly using this class.");

            SqlContext context = (SqlContext) Context;
            return context.GetConnectionInternal(database);
        }

        private TableDefinition GetOrphans(TableDefinition definition, CacheItem cache)
        {
            PropertyInfo[] properties = cache.GetAllProperties();
            TableDefinition orphan = new TableDefinition();

            for (int i = 0; i < definition.Names.Count; i++)
            {
                bool flag = false;

                foreach (PropertyInfo prop in properties)
                {
                    string propname = Formatter.GetPropertyName(prop, ".");

                    if (propname == definition.Names[i])
                    {
                        flag = true;
                        break;
                    }
                }

                //Set up orphan table definition
                if (!flag)
                {
                    orphan.Names.Add(definition.Names[i]);
                    orphan.Types.Add(definition.Types[i]);
                    orphan.MaxLengths.Add(definition.MaxLengths[i]);
                    orphan.Nulls.Add(definition.Nulls[i]);
                }
            }

            return orphan;
        }

        private bool TableExists(string name)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '");
            builder.Append(name);
            builder.Append("'");

            //Determine if table created
            System.Data.SqlClient.SqlConnection connection = GetConnection();
            System.Data.SqlClient.SqlCommand command = connection.CreateCommand();
            command.CommandText = builder.ToString();
            
            object result = null;

            try
            {
                result = command.ExecuteScalar();
            }
            finally
            {
                command.Dispose();
                connection.Dispose();
            }

            return (result != null);
        }

        #endregion
    }
}